package cn.dckeji.dcssm.common.utils;



import cn.dckeji.dcssm.entity.Park;
import cn.dckeji.dcssm.entity.Users;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

/**
 *
 */
public class ExcelUtils {


    /**
     * 读取Excel文件修改到数据库
     *
     * @throws IOException
     */
    public static List<Users> readExcelToSql(String excelUrl)
            throws IOException {

        String suffix = excelUrl.substring(excelUrl.lastIndexOf(".") + 1)
                .trim();
        if ("xls".equals(suffix)) {
            // 2003
            InputStream in = new FileInputStream(excelUrl);
            HSSFWorkbook workbook = new HSSFWorkbook(in);
            List<Users> list = new ArrayList<>();
            HSSFSheet sheet = workbook.getSheetAt(0);
            for (int i=1;i<=sheet.getLastRowNum();i++){
                Users study = new Users();
                HSSFRow row = sheet.getRow(i);// 获取到第i列的行数据(表格行)
                int column = row.getPhysicalNumberOfCells();//列数
                for (int j = 0; j<column; j++){
                    HSSFCell cell = row.getCell(j);
                    if(cell!=null){
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                    }
                    if(j==0){
                        study.setUserName(cell==null?"":cell.getStringCellValue());
                    }
                    if(j==1){
                        study.setUserPhone(cell==null?"":cell.getStringCellValue());
                    }
                    if(j==2){
                        study.setUserPwd(cell==null?"":cell.getStringCellValue());
                    }
                }
                study.setGmtCreate(LocalDateTime.now());
                list.add(study);
            }

            return list;

        } else if ("xlsx".equals(suffix)) {
            // 2007
            InputStream in = new FileInputStream(excelUrl);
            XSSFWorkbook workbook = new XSSFWorkbook(in);
            List<Users> list = new ArrayList<>();
            XSSFSheet sheet = workbook.getSheetAt(0);
            for (int i=1;i<=sheet.getLastRowNum();i++){
                Users study = new Users();
                XSSFRow row = sheet.getRow(i);// 获取到第i列的行数据(表格行)
                int column = row.getPhysicalNumberOfCells();//列数
                for (int j = 0; j<column; j++){
                    XSSFCell cell = row.getCell(j);
                    if(cell!=null){
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                    }
                    if(j==0){
                        study.setUserName(cell==null?"":cell.getStringCellValue());
                    }
                    if(j==1){
                        study.setUserPhone(cell==null?"":cell.getStringCellValue());
                    }
                    if(j==2){
                        study.setUserPwd(cell==null?"":cell.getStringCellValue());
                    }
                }
                study.setGmtCreate(LocalDateTime.now());
                list.add(study);
            }
            return list;
        }
        return null;
    }


    /**
     * 读取Excel文件修改到数据库
     *
     * @throws IOException
     */
    public static List<Park> readExcelToSql2(String excelUrl)
            throws IOException {

        String suffix = excelUrl.substring(excelUrl.lastIndexOf(".") + 1)
                .trim();
        if ("xls".equals(suffix)) {
            // 2003
            InputStream in = new FileInputStream(excelUrl);
            HSSFWorkbook workbook = new HSSFWorkbook(in);
            List<Park> list = new ArrayList<>();
            HSSFSheet sheet = workbook.getSheetAt(0);
            for (int i=1;i<=sheet.getLastRowNum();i++){
                Park study = new Park();
                HSSFRow row = sheet.getRow(i);// 获取到第i列的行数据(表格行)
                int column = row.getPhysicalNumberOfCells();//列数
                for (int j = 0; j<column; j++){
                    HSSFCell cell = row.getCell(j);
                    if(cell!=null){
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                    }
                    if(j==0){
                        study.setParkNo(cell==null?"":cell.getStringCellValue());
                    }
                    if(j==1){
                        study.setParkPrice(cell==null?"":cell.getStringCellValue());
                    }
                }
                study.setGmtCreate(LocalDateTime.now());
                list.add(study);
            }

            return list;

        } else if ("xlsx".equals(suffix)) {
            // 2007
            InputStream in = new FileInputStream(excelUrl);
            XSSFWorkbook workbook = new XSSFWorkbook(in);
            List<Park> list = new ArrayList<>();
            XSSFSheet sheet = workbook.getSheetAt(0);
            for (int i=1;i<=sheet.getLastRowNum();i++){
                Park study = new Park();
                XSSFRow row = sheet.getRow(i);// 获取到第i列的行数据(表格行)
                int column = row.getPhysicalNumberOfCells();//列数
                for (int j = 0; j<column; j++){
                    XSSFCell cell = row.getCell(j);
                    if(cell!=null){
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                    }
                    if(j==0){
                        study.setParkNo(cell==null?"":cell.getStringCellValue());
                    }
                    if(j==1){
                        study.setParkPrice(cell==null?"":cell.getStringCellValue());
                    }
                }
                study.setGmtCreate(LocalDateTime.now());
                list.add(study);
            }
            return list;
        }
        return null;
    }


}
